package com.lanxin.oa.fiancial.dao;

import com.lanxin.base.BaseDaoImpl;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Repository
public class BxInformalTjDaoImpl extends BaseDaoImpl implements BxInformalTjDao{

	private SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd");
	
	public List selectdepttj(Date startDate, Date endDate) {
		String hql="select dept.deptName,sum(bx.bookAmount),sum(bx.applyAmount) from "
				+ " BxInformal bx , ZzDepartment dept  where dept.oid=bx.deptid ";
		if (startDate != null && !startDate.equals("")) {
			hql = hql + " and  bx.createDate >= '" + sim.format(startDate) + "'  ";
		}
		if (endDate != null && !endDate.equals("")) {
			
			hql = hql + " and bx.createDate <= '" + sim.format(endDate) + "' ";
		}
		hql = hql + " group by dept.deptName" ;
		List  list = queryAll(hql);
		return list;

	}

	@Override
	public List selecttypetj(Date startDate, Date endDate) {
		String hql="select type.name,sum(bxmx.amount),sum(bxmx.applyAmount) from"
				+ " BxInformal bx, BxInformaltype  type ,BxInformaldetail bxmx "
				+ "where type.oid=bxmx.typeid and bxmx.parentid=bx.oid ";
		if (startDate != null && !startDate.equals("")) {
			hql = hql + " and  bx.createDate >= '" + sim.format(startDate) + "' ";
		}
		if (endDate != null && !endDate.equals("")) {
			
			hql = hql + " and bx.createDate <= '" + sim.format(endDate) + "' ";
		}
		  hql=hql+"group by type.name";
		return queryAll(hql);
	}

	@Override
	public List selecttj() {
		String hql="select dept.deptName,sum(bx.applyAmount),sum(bx.bookAmount) from "
				+ " BxInformal bx , ZzDepartment dept  where dept.oid=bx.deptid group by dept.deptName";
		
		return queryAll(hql);
	}

}
